from django.db import models

from django.db import connection
from django.db import transaction


class TimeManager(models.Manager):
    
    def take_time_with_day(self, user_id, day):
        cursor = connection.cursor()
        cursor.execute("""SELECT p.name_project, a.name_activity, t.time,
                                 t.details, t.id, t.is_completed
                          FROM timesheet_activityproject ap
                                   INNER JOIN timesheet_project p
                                   ON p.id = ap.project_id
                                   INNER JOIN timesheet_activity a
                                   ON a.id = ap.activity_id
                                   INNER JOIN timesheet_time t
                                   ON ap.id = t.activity_project_id,
                               timesheet_date d
                          WHERE d.id = t.day_id AND
                                ap.user_id = %s AND d.id = %s
                       """, [user_id, day.id])

        return cursor.fetchall()

    def update_time(self, time_id, value_time, value_details):
        cursor = connection.cursor()
        cursor.execute("""UPDATE timesheet_time SET time = %s, details = %s
                          WHERE id = %s
                       """, [value_time, value_details, time_id])

        transaction.commit_unless_managed()

        return

    def complete_time(self, id_user, begin_day, end_day):
        cursor = connection.cursor()
        cursor.execute("""UPDATE timesheet_time, timesheet_activityproject,
                                 timesheet_date
                          SET timesheet_time.is_completed = 1
                          WHERE timesheet_time.is_completed = 0
                                AND timesheet_activityproject.id =
                                timesheet_time.activity_project_id AND
                                timesheet_activityproject.user_id = %s
                                AND timesheet_date.id = timesheet_time.day_id
                                AND (timesheet_date.day BETWEEN %s AND %s)
                       """, [id_user, begin_day, end_day])

        transaction.commit_unless_managed()

        return

    def take_time_not_complete(self, user_id):
        return self.extra(tables=['timesheet_activityproject'],
                               where=['timesheet_activityproject.id = \
                                       timesheet_time.activity_project_id',
                                      'timesheet_time.is_completed = 0',
                                      'timesheet_activityproject.user_id = %s' % user_id])

    def take_time_interval_date(self, user_id, initial_date, end_date):
        return self.extra(tables=['timesheet_activityproject', 'timesheet_date'],
                               where=['timesheet_activityproject.id = \
                                       timesheet_time.activity_project_id',
                                      'timesheet_time.is_confirmed = 1',
                                      'timesheet_activityproject.user_id = %s' % user_id,
                                      'timesheet_time.day_id = timesheet_date.id',
                                      "timesheet_date.day >= '%s'" % initial_date,
                                      "timesheet_date.day <= \
                                      '%s'" % end_date]).distinct().values_list('id', 'time')

    def take_project_time_interval_date(self, user_id, project_id, initial_date, end_date):
        return self.extra(tables=['timesheet_activityproject',
                                  'timesheet_project', 'timesheet_date'],
                               where=['timesheet_project.id = \
                                       timesheet_activityproject.project_id',
                                      'timesheet_project.id = %s' % project_id,
                                      'timesheet_activityproject.id = \
                                       timesheet_time.activity_project_id',
                                      'timesheet_time.is_confirmed = 1',
                                      'timesheet_activityproject.user_id = %s' % user_id,
                                      'timesheet_time.day_id = timesheet_date.id',
                                      "timesheet_date.day >= '%s'" % initial_date,
                                      "timesheet_date.day <= \
                                      '%s'" % end_date]).distinct().values_list('id', 'time')

    def take_project_all_time_interval_date(self, project_id, initial_date, end_date):
        return self.extra(tables=['timesheet_activityproject',
                                  'timesheet_project', 'timesheet_date'],
                               where=['timesheet_project.id = \
                                       timesheet_activityproject.project_id',
                                      'timesheet_project.id = %s' % project_id,
                                      'timesheet_activityproject.id = \
                                       timesheet_time.activity_project_id',
                                      'timesheet_time.is_confirmed = 1',
                                      'timesheet_time.day_id = timesheet_date.id',
                                      "timesheet_date.day >= '%s'" % initial_date,
                                      "timesheet_date.day <= \
                                      '%s'" % end_date]).distinct().values_list('id', 'time')

    def take_all_time_day_interval(self, user_id, day_initial, day_end):
        cursor = connection.cursor()
        cursor.execute("""SELECT d.day, p.name_project, a.name_activity,
                                 t.time, t.id, t.is_completed
                          FROM timesheet_activityproject ap
                                   INNER JOIN timesheet_project p
                                   ON p.id = ap.project_id
                                   INNER JOIN timesheet_activity a
                                   ON a.id = ap.activity_id
                                   INNER JOIN timesheet_time t
                                   ON ap.id = t.activity_project_id,
                               timesheet_date d
                          WHERE ap.user_id = %s
                                AND d.id = t.day_id
                                AND (d.day BETWEEN %s AND %s) ORDER BY d.day
                       """, [user_id, day_initial, day_end])

        return cursor.fetchall()


class ProjectManager(models.Manager):

    def take_projects(self, user_id):
        return self.extra(tables=['manager_projectmanageruser'],
                          where=['manager_projectmanageruser.his_user_id = %s' % user_id,
                                 'timesheet_project.id = \
                                  manager_projectmanageruser.project_id'])


class DateManager(models.Manager):

    def take_dates(self, user_id):
        return self.extra(tables=['timesheet_activityproject', 'timesheet_time'],
                          where=['timesheet_activityproject.user_id = %s' % user_id,
                                 'timesheet_activityproject.id = \
                                  timesheet_time.activity_project_id',
                                 'timesheet_date.id = \
                                  timesheet_time.day_id']).distinct().order_by('day').values_list('day',
                                                                                                  flat=True)


class ActivityProjectManager(models.Manager):

    def take_activityproject(self, user_id, project_id, activity_id):
        return self.extra(where=['timesheet_activityproject.user_id = %s' % user_id,
                                 'timesheet_activityproject.project_id = \
                                  %s' % project_id,
                                 'timesheet_activityproject.activity_id = \
                                  %s' % activity_id])[0]

    def take_exist_activityproject(self, user_id, project, activity):
        return self.extra(tables=['timesheet_project', 'timesheet_activity'],
                          where=['timesheet_activityproject.user_id = %s' % user_id,
                                 'timesheet_activityproject.project_id = \
                                  timesheet_project.id',
                                 'timesheet_activityproject.activity_id = \
                                  timesheet_activity.id',
                                 "timesheet_project.name_project = '%s'" % project,
                                 "timesheet_activity.name_activity = \
                                 '%s'" % activity])[0]
